package com.hengtiansoft.lfy.db;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.text.TextUtils;


import com.hengtiansoft.lfy.been.ChangeWords;
import com.hengtiansoft.lfy.been.Child;
import com.hengtiansoft.lfy.been.ChildType;
import com.hengtiansoft.lfy.utils.Util;

import java.util.ArrayList;
import java.util.List;

public class CL_DBManager {
    static private CL_DBManager dbMgr = new CL_DBManager();
    private CL_DbOpenHelper dbHelper;

    void onInit(Context context) {
        dbHelper = CL_DbOpenHelper.getInstance(context);
    }

    public static synchronized CL_DBManager getInstance() {
        return dbMgr;
    }

    //前面已经考虑到上次isneed更新问题
    synchronized public void saveChildCategroy(int categroyId, List<Child> childList){
        SQLiteDatabase db = dbHelper.getWritableDatabase();
        if (db.isOpen()) {
            db.delete("childCategroy", "categroy_id=?", new String[]{categroyId+""});
            for (Child child : childList) {
                ContentValues values = new ContentValues();
                values.put("categroy_id", categroyId);
                values.put("service_child_id", child.getId());
                values.put("child_name",child.getName());
                values.put("child_downloadsize",child.getSize());
                values.put("child_downloadcount",child.getRemark());
                values.put("child_img_url",child.getImagepath());
                values.put("datatime",child.getCreate_time());
                values.put("zip_name",child.getZip_name());
                if(child.getDownload_type()!=null){
                    values.put("download_type",child.getDownload_type());
                }
                if(child.getIsneedUpdate()!=null) {
                    values.put("isneedUpdate", child.getIsneedUpdate());
                }
                db.insert("childCategroy", null, values);
            }
        }
    }

    //更新一级分类已下载
    synchronized public void downloadOK(String childid, String time){
        SQLiteDatabase db = dbHelper.getWritableDatabase();
        if (db.isOpen()) {
            ContentValues values = new ContentValues();
            values.put("download_type", time);
            db.update("childCategroy", values, "service_child_id = ?", new String[]{childid + ""});
        }
    }

    //更新一级分类已经更新
    synchronized public void upDateChildCategroy_isNeed_off(String childid){
        SQLiteDatabase db = dbHelper.getWritableDatabase();
        if (db.isOpen()) {
            ContentValues values = new ContentValues();
            values.put("isneedUpdate", "0");
            db.update("childCategroy", values, "service_child_id = ?", new String[]{childid + ""});
        }
    }

    //更新二级分类已经更新
    synchronized public void upDatechildtype_isNeed_off(String childid, String child_fen_id){
        SQLiteDatabase db = dbHelper.getWritableDatabase();
        if (db.isOpen()) {
            ContentValues values = new ContentValues();
            values.put("isneedUpdate", "0");
            db.update("childtype", values, "child_id = ? and child_fen_id=?", new String[]{childid + "", child_fen_id});
        }
    }
    //
    //获取所有场景分类
    synchronized public List<Child> getChildByCategroyId(Integer categroyId) {
        List list = new ArrayList<Child>();
        SQLiteDatabase db = dbHelper.getWritableDatabase();
        if (db.isOpen()) {
            String sql = "select * from childCategroy where categroy_id="
                    + categroyId;
            Cursor cursor = db.rawQuery(sql, null);

            Child child = null;
            while (cursor.moveToNext()) {
                child = new Child();
                child.setId(Integer.valueOf(cursor.getString(cursor
                        .getColumnIndex("service_child_id"))));
                child.setIs_use(Integer.valueOf(cursor.getString(cursor
                        .getColumnIndex(Util.CHILD_CATEGROYID))));
                child.setName(cursor.getString(cursor
                        .getColumnIndex(Util.CHILD_NAME)));
                child.setChildHeat(Integer.valueOf(cursor.getString(cursor
                        .getColumnIndex(Util.CHILD_HEAT))));
                child.setRemark(cursor.getString(cursor
                        .getColumnIndex("child_downloadcount")));//setDownload_num
                child.setSize(cursor.getString(cursor
                        .getColumnIndex("child_downloadsize")));
                child.setImagepath(cursor.getString(cursor
                        .getColumnIndex("child_img_url")));
                child.setCreate_time(cursor.getString(cursor
                        .getColumnIndex("datatime")));
                child.setIsneedUpdate(cursor.getString(cursor
                        .getColumnIndex("isneedUpdate")));
                child.setZip_name(cursor.getString(cursor
                        .getColumnIndex("zip_name")));
                child.setDownload_type(cursor.getString(cursor
                        .getColumnIndex("download_type")));
                list.add(child);
                child = null;
            }
            cursor.close();
        }
        return list;
    }
    //获取已下载的场景
    synchronized public List<Child> getChildDownloadByCategroyId(Integer categroyId) {
        List list = new ArrayList<Child>();
        SQLiteDatabase db = dbHelper.getWritableDatabase();
        if (db.isOpen()) {
            String sql = "select * from childCategroy where categroy_id="
                    + categroyId+" and download_type<>0";
            Cursor cursor = db.rawQuery(sql, null);

            Child child = null;
            while (cursor.moveToNext()) {
                child = new Child();
                child.setId(Integer.valueOf(cursor.getString(cursor
                        .getColumnIndex("service_child_id"))));
                child.setIs_use(Integer.valueOf(cursor.getString(cursor
                        .getColumnIndex(Util.CHILD_CATEGROYID))));
                child.setName(cursor.getString(cursor
                        .getColumnIndex(Util.CHILD_NAME)));
                child.setChildHeat(Integer.valueOf(cursor.getString(cursor
                        .getColumnIndex(Util.CHILD_HEAT))));
                child.setDownload_num(cursor.getString(cursor
                        .getColumnIndex("child_downloadcount")));
                child.setSize(cursor.getString(cursor
                        .getColumnIndex("child_downloadsize")));
                child.setImagepath(cursor.getString(cursor
                        .getColumnIndex("child_img_url")));
                child.setCreate_time(cursor.getString(cursor
                        .getColumnIndex("datatime")));
                child.setIsneedUpdate(cursor.getString(cursor
                        .getColumnIndex("isneedUpdate")));
                child.setZip_name(cursor.getString(cursor
                        .getColumnIndex("zip_name")));
                child.setDownload_type(cursor.getString(cursor
                        .getColumnIndex("download_type")));
                list.add(child);
                child = null;
            }
            cursor.close();
        }
        return list;
    }
    //保存分类
    synchronized public void saveChildType(List<ChildType> childTypeList, String child_id){
        SQLiteDatabase db = dbHelper.getWritableDatabase();
        if (db.isOpen()) {
            db.delete("childtype", "child_id=?", new String[]{child_id});
            for(ChildType childType:childTypeList){
                ContentValues values = new ContentValues();
                values.put("child_id", childType.getChild_id());
                values.put("child_fen_id",childType.getChild_fen_id());
                values.put("child_fen_detail",childType.getChild_fen_detail());
                values.put("child_fen_e_detail",childType.getChild_fen_e_detail());
                values.put("isneedUpdate",childType.getIsneedUpdate());
                db.insert("childtype", null, values);
            }
        }
    }
    //通过child_id,child_fen_id查询所有分类
    synchronized public List<ChildType> getChildType(String child_id){
        List list = new ArrayList<ChildType>();
        SQLiteDatabase db = dbHelper.getWritableDatabase();
        if (db.isOpen()) {
            String sql="select * from childtype where child_id="+child_id;
            Cursor cursor = db.rawQuery(sql, null);
            ChildType childType = null;
            while (cursor.moveToNext()) {
                childType=new ChildType();
                childType.setChild_id(cursor.getString(cursor
                        .getColumnIndex("child_id")));
                childType.setChild_fen_id(cursor.getString(cursor
                        .getColumnIndex("child_fen_id")));
                childType.setChild_fen_detail(cursor.getString(cursor
                        .getColumnIndex("child_fen_detail")));
                childType.setChild_fen_e_detail(cursor.getString(cursor
                        .getColumnIndex("child_fen_e_detail")));
                childType.setIsneedUpdate(cursor.getString(cursor
                        .getColumnIndex("isneedUpdate")));
                list.add(childType);
                childType=null;
            }
            cursor.close();
        }
        return list;
    }
    //查询words
    synchronized public List<ChangeWords> getSimilarResult(String child_id, String childtype_id, String chinese,
                                                           String english, int page){
        List list = new ArrayList<ChangeWords>();
        SQLiteDatabase db = dbHelper.getWritableDatabase();
        if (db.isOpen()) {
            int countInOnePage = Util.COUNTINONEPAGE;
            String sql = "select * from words where 1=1 ";
            if ((chinese == null || "".equals(chinese)) && english != null) {
                sql += " and english like '%"
                        + strFilter(english) + "%'";
            } else if ((english == null || "".equals(english)) && chinese != null) {
                sql += " and chinese like '%"
                        + strFilter(chinese) + "%'";
            }
            if (child_id == null || "".equals(child_id)) {

            } else {
                sql += " and child_id=" + child_id;
            }
            if (childtype_id == null || "".equals(childtype_id)) {

            } else {
                sql += " and childtype_id=" + childtype_id;
            }
            sql += " order by words_id limit " + countInOnePage
                    + " offset " + (page - 1) * countInOnePage;
          //  Log.i("supeng","sql="+sql);
            Cursor cursor = db.rawQuery(sql, null);
            ChangeWords words = null;
            while (cursor.moveToNext()) {
                words = new ChangeWords();
                words.setChild_id(Integer.valueOf(cursor.getString(cursor
                        .getColumnIndex("child_id"))));
                words.setWords_id(Integer.valueOf(cursor.getString(cursor
                        .getColumnIndex("words_service_id"))));
                words.setId(Integer.valueOf(cursor.getString(cursor
                        .getColumnIndex("words_service_id"))));
                words.setScenes_id(Integer.valueOf(cursor.getString(cursor
                        .getColumnIndex("childtype_id"))));
                words.setDes_content(cursor.getString(cursor
                        .getColumnIndex("english")));
                words.setSrc_content(cursor.getString(cursor
                        .getColumnIndex("chinese")));
                words.setEnglish_mp3path(cursor.getString(cursor
                        .getColumnIndex("english_mp3path")));
                words.setWords_iscollect(cursor.getString(cursor
                        .getColumnIndex("words_iscollect")));
                words.setCollect_server_id(cursor.getString(cursor
                        .getColumnIndex("collect_server_id")));
                list.add(words);
                words = null;
            }
            cursor.close();
        }
        return list;
    }
    // 过滤单引号
    public String strFilter(String str) {
        return str.replace("'", "''");
    }
    //查询已收藏的words
    synchronized public List<ChangeWords> searchWords(String childid, String child_fen_id){
        List list = new ArrayList<ChangeWords>();
        SQLiteDatabase db = dbHelper.getWritableDatabase();
        if (db.isOpen()) {
            String sql = "select * from words where child_id="+childid+" and childtype_id="+child_fen_id+" and words_iscollect='Y'";
            Cursor cursor = db.rawQuery(sql, null);
            ChangeWords words = null;
            while (cursor.moveToNext()) {
                words = new ChangeWords();
                words.setChild_id(Integer.valueOf(cursor.getString(cursor
                        .getColumnIndex("child_id"))));
                words.setWords_id(Integer.valueOf(cursor.getString(cursor
                        .getColumnIndex("words_service_id"))));
                words.setId(Integer.valueOf(cursor.getString(cursor
                        .getColumnIndex("words_service_id"))));
                words.setScenes_id(Integer.valueOf(cursor.getString(cursor
                        .getColumnIndex("childtype_id"))));
                words.setDes_content(cursor.getString(cursor
                        .getColumnIndex("english")));
                words.setSrc_content(cursor.getString(cursor
                        .getColumnIndex("chinese")));
                words.setEnglish_mp3path(cursor.getString(cursor
                        .getColumnIndex("english_mp3path")));
                words.setWords_iscollect(cursor.getString(cursor
                        .getColumnIndex("words_iscollect")));
                words.setCollect_server_id(cursor.getString(cursor
                        .getColumnIndex("collect_server_id")));
                list.add(words);
                words = null;
            }
            cursor.close();
        }
        return list;
    }
    //保存words
    synchronized public void saveWords(List<ChangeWords> wordsList, String childid, String child_fen_id){
        SQLiteDatabase db = dbHelper.getWritableDatabase();
        if (db.isOpen()) {
            db.delete("words", "child_id=? and childtype_id=?", new String[]{childid,child_fen_id});
            for(ChangeWords changeWords:wordsList){
                ContentValues values = new ContentValues();
                values.put("child_id",childid);
                values.put("chinese",changeWords.getSrc_content());
                values.put("english",changeWords.getDes_content());
                values.put("childtype_id",child_fen_id);
                values.put("words_service_id",changeWords.getId());
                db.insert("words", null, values);
            }
        }
    }
    //更新收藏状态
    synchronized public void upDateCollect(String id, String type, String serverId){
        SQLiteDatabase db = dbHelper.getWritableDatabase();
        if (db.isOpen()) {
            ContentValues values = new ContentValues();
            values.put("collect_server_id", serverId);
            values.put("words_iscollect",type);
            db.update("words", values, "words_service_id = ?", new String[]{id + ""});
        }
    }
    //删除收藏状态
    synchronized public void deleteCollect( String type, String serverId){
        SQLiteDatabase db = dbHelper.getWritableDatabase();
        if (db.isOpen()) {
            ContentValues values = new ContentValues();
            values.put("words_iscollect",type);
            if(TextUtils.isEmpty(serverId)){

                db.update("words", values,null,null);
            }else{
                db.update("words", values, "collect_server_id = ?", new String[]{serverId + ""});
            }

        }
    }

    //查询单个words
    synchronized public ChangeWords getWords(String words_id){
        SQLiteDatabase db = dbHelper.getWritableDatabase();
        ChangeWords words = null;
        if (db.isOpen()) {
            String sql = "select * from words where words_service_id="+words_id;
            Cursor cursor = db.rawQuery(sql, null);
            if (cursor.moveToNext()) {
                words.setChild_id(Integer.valueOf(cursor.getString(cursor
                        .getColumnIndex("child_id"))));
                words.setWords_id(Integer.valueOf(cursor.getString(cursor
                        .getColumnIndex("words_service_id"))));
                words.setScenes_id(Integer.valueOf(cursor.getString(cursor
                        .getColumnIndex("childtype_id"))));
                words.setDes_content(cursor.getString(cursor
                        .getColumnIndex("english")));
                words.setSrc_content(cursor.getString(cursor
                        .getColumnIndex("chinese")));
                words.setEnglish_mp3path(cursor.getString(cursor
                        .getColumnIndex("english_mp3path")));
            }
        }
        return words;
    }
    //修改words
    synchronized public void updateWords(String wordspath, String words_id){
        SQLiteDatabase db = dbHelper.getWritableDatabase();
        if (db.isOpen()) {
            ContentValues values = new ContentValues();
            values.put("english_mp3path", wordspath);
            db.update("words", values, "words_service_id = ?", new String[]{words_id + ""});
        }
    }
    //批量修改words
    synchronized public void updateALLWords(List<ChangeWords> changeWordsList){
        SQLiteDatabase db = dbHelper.getWritableDatabase();
        if (db.isOpen()) {
            for(ChangeWords changeWords:changeWordsList) {
                ContentValues values = new ContentValues();
                values.put("english_mp3path", changeWords.getEnglish_mp3path());
                db.update("words", values, "words_service_id = ?", new String[]{changeWords.getWords_id() + ""});
            }
        }
    }
    //-----------------------------------------------------------------------------------------------------


}